﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using webapi.Entity;

namespace webapi.Models
{
  public class CourseService
  {
    //修改课程信息
    static public int updateCourse(Course course)
    {
      string sql = string.Format("update course set course_id='{0}', name='{1}', point={2}, is_must={3} where course_id='{0}'", course.course_id, course.name, course.point, course.is_must);
      return DB.ExecuteSql(sql);
    }

    //添加课程
    static public int insertCourse(Course course)
    {
      string sql = string.Format("insert into course(course_id, name, point, is_must) values('{0}','{1}',{2},{3})", course.course_id, course.name, course.point, course.is_must);
      return DB.ExecuteSql(sql);
    }

    //删除课程
    static public int deleteCourse(string id)
    {
      DataRowCollection dr = getCourseById(id);
      int len = dr.Count;
      for (int i = 0; i < len; i++)
      {
        SetUpCourseService.deleteSetUpCourse(dr[i][2].ToString());
      }
      string sql = "delete from course where course_id='" + id + "'";
      return DB.ExecuteSql(sql);
    }

    //根据课程名查找课程
    static public DataRowCollection getCoursesByName(string name)
    {
      string sql = "select * from course where name like '%" + name + "%'";
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }

    //获取全部课程
    static public DataRowCollection getAllCourse()
    {
      string sql = "select * from course";
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }

    //获取全部开设的课程
    static public DataRowCollection getAllSetUpCourse()
    {
      string sql = "select * from set_up_course";
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }

    //获取开设的课程
    static public DataRowCollection getCourseList()
    {
      string sql = "select b.name, a.course_id, a.class_course_id, b.point, a.day, a.time, a.start, a.last from set_up_course as a join course as b on a.course_id = b.course_id where a.id in (select max(id) from set_up_course group by course_id)";
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }

    static public DataRowCollection getAllClass()
    {
      string sql = "select b.name, a.course_id, a.class_course_id, b.point, a.day, a.time, a.start, a.last from set_up_course as a join course as b on a.course_id = b.course_id";
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }

    //根据id获取个人已选的课程
    static public DataRowCollection getOwnCourse(string id)
    {
      string sql = "select c.name, b.course_id, a.class_course_id, c.point, b.day, b.time, b.start, b.last from select_course as a join set_up_course as b on a.class_course_id = b.class_course_id join course as c on b.course_id = c.course_id where a.stu_id = '" + id + "'";
      DataSet ds = DB.ExecuteSelectSql(sql);
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }

    //根据课程班级号获取课程信息
    static public DataRowCollection getCourseById(string id)
    {
      string sql = "select b.name, a.course_id, a.class_course_id, b.point, a.day, a.time, a.start, a.last from set_up_course as a join course as b on a.course_id = b.course_id where a.course_id = '" + id + "'";
      DataSet ds = DB.ExecuteSelectSql(sql);
      return DB.ExecuteSelectSql(sql).Tables[0].Rows;
    }
  }
}